import MySQLdb
import hashlib
import traceback

class databaseCommon():
    def __init__(self):
        self.m_conn =  MySQLdb.connect("localhost", "root", "zhaoming832002", "proxy", charset='utf8' )

    _instance = None

    def __new__(cls, *args, **kw):
        if not cls._instance:
            cls._instance = super(databaseCommon, cls).__new__(cls, *args, **kw)
        return cls._instance

    def __del__(self):
        self.m_conn.close()

    def getConn(self):
        return  self.m_conn

class databaseCore():
    m_conn = databaseCommon().getConn()

    def getID(self, url, code='utf-8'):
        try:
            md5 = hashlib.md5()
            if code!="":
                md5.update(url.encode(code))  # 注意转码
            else:
                md5.update(url)  # 注意转码

            res = md5.hexdigest()
            return res
        except Exception as e:
            print('e.message:\t%s'% str(e))
            print('traceback.print_exc():')
            traceback.print_exc()

    def findID(self, proxy, nameID="id"):
        uuid = self.getID(url=proxy)
        c = self.m_conn.cursor()

        sql = "select count(*) from proxy where %(nameID)s = \'%(id)s\'" % {"nameID":nameID, "id": uuid}
        c.execute(sql)

        for i in range(c.rowcount):
            row = c.fetchone()
            iCount = row[0]

        if iCount > 0:
            return True
        return False

    def exeSql(self, sql, data=[]):
        cursor = self.m_conn.cursor()
        sql = sql.replace('?', "%s")
        try:
            if len(data) > 0:
                cursor.execute(sql, tuple(data))
            else:
                cursor.execute(sql)
        except Exception as e:
            print('e.message:\t%s' % str(e))
            print('traceback.print_exc():')
            traceback.print_exc()
        return  cursor

    def exeSqlWithModify(self, sql, data=[], isCommitNow=True):
        try:
            cursor = self.m_conn.cursor()
            sql = sql.replace('?', "%s")
            if len(data) > 0:
                cursor.execute(sql, tuple(data))
            else:
                cursor.execute(sql)
            if isCommitNow:
                self.m_conn.commit()
        except Exception as e:
            print('e.message:\t%s' %str(e))
            print('traceback.print_exc():')
            traceback.print_exc()

    def GetColIndex(self, cursor, colName):
        num_fields = len(cursor.description)
        field_names = [i[0] for i in cursor.description]
        for i in range(num_fields):
            if field_names[i] == colName:
                return i
        return  -1
    def IsHasDataID(self, tableName, compareValue, colName="id"):
        if colName=="":
            iCount = self.GetDataCount(tableName, [], [], [])
        else:
            iCount = self.GetDataCount(tableName, [compareValue], [colName], ["="])
        return  iCount>0

    def IsHasData(self, tableName, compareValue, colName="id", code='utf-8'):
        return self.IsHasDataID(tableName, self.getID(compareValue, code), colName)

    def GetData(self, tableName, compareValue, colName="id", serchInx=[0]):
        if colName=="":
            return self.GetDataMulCondition(tableName, [], [], [], serchInx)
        else:
            return  self.GetDataMulCondition(tableName, [compareValue], [colName], ["="], serchInx)

    def GetDataCount(self, tableName, compareValue=[], colName=[], compareRule=[]):
        sql = "select count(*) from %s " % tableName
        data = []

        if len(colName) > 0:
            sql = sql + "where"
            for i in range(len(colName)):
                sql = sql + " %(colName)s %(rule)s ?" % {"colName": colName[i], "rule": compareRule[i]}
                if i != len(colName) - 1:
                    sql = sql + "and "
                else:
                    sql = sql + " "
            for i in range(len(colName)):
                data.append(compareValue[i])
            sql = sql.replace('?', "%s")

        cursor = self.exeSql(sql, data)
        ret=-1
        if cursor.rowcount> 0:
            row = cursor.fetchone()
            ret = row[0]
        return ret

    def GetDataMulCondition(self, tableName, compareValue=[], colName=[], compareRule=[], serchInx=[0]):
        sql="select * from %s "%tableName

        data = []
        if len(colName) > 0:
            sql=sql+"where"
            for i in range(len(colName)):
                sql=sql+ " %(colName)s %(rule)s ?"%{"colName":colName[i],"rule":compareRule[i]}
                if i != len(colName) - 1:
                    sql = sql + "and "
                else:
                    sql = sql + " "
            sql = sql.replace('?', "%s")
            for i in range(len(colName)):
                data.append(compareValue[i])

        cursor = self.exeSql(sql, data)
        ret=[]
        for i in range(cursor.rowcount):
            row = cursor.fetchone()
            retline={}
            for j in range(len(serchInx)):
                retline[serchInx[j]]=row[self.GetColIndex(cursor,serchInx[j])]
            ret.append(retline)
        return ret

    def UpdateData(self, tableName, valueForCompare=[], colForCompare=[], compareRule=[], valueForSet=[], colForSet=[], isCommitNow=True):
        if self.GetDataCount(tableName, valueForCompare, colForCompare,compareRule) <=0:
            return  False

        sql = 'update %s set '%tableName
        data = []
        for i in range(len(colForSet)):
            sql = sql + " %(colName)s = ?" % {"colName": colForSet[i]}
            if i != len(colForSet) - 1:
                sql = sql + ","
            else:
                sql = sql + " "
            data.append(valueForSet[i])
        if len(colForCompare) > 0:
            sql = sql+" where "
            for i in range(len(colForCompare)):
                sql = sql + " %(colName)s %(rule)s ?" % {"colName": colForCompare[i], "rule": compareRule[i]}
                data.append(valueForCompare[i])

        self.exeSqlWithModify(sql, data,isCommitNow)
        return True

    def InsertOrUpdateData(self, tableName, valueForCompare=[], colForCompare=[], compareRule=[], valueForSet=[],  colForSet=[], isCommitNow=True):
        if self.GetDataCount(tableName, valueForCompare, colForCompare, compareRule) > 0 and len(colForCompare)>0:
            return self.UpdateData(tableName, valueForCompare, colForCompare, compareRule, valueForSet, colForSet)

        sql = "insert into %s("%tableName

        data = []
        for i in range(len(valueForSet)):
            sql = sql + " %(colName)s" % {"colName": colForSet[i]}
            if i != len(colForSet) - 1:
                sql = sql + ","
            else:
                sql = sql + ") VALUES("

        for i in range(len(valueForSet)):
            sql = sql + " ?"
            data.append(valueForSet[i])
            if i != len(colForSet) - 1:
                sql = sql + ","
            else:
                sql = sql + ")"

        self.exeSqlWithModify(sql, data,isCommitNow)

    def CommitData(self):
        self.m_conn.commit()
